Large enterprise application solutions use database management systems (DBMS) such as DB2®, Informix®, Oracle®, and MS SQL Server to store and retrieve data. The schemas for these solutions are very complex, including tens of thousands of tables and indexes. The number of objects in the database poses significant challenges to typical database administration (DBA) operations such as backup, reorganization, statistics collection, and database space growth management.
In practice, database administration operations typically follow one of two approaches: performing the operations on all the objects, or performing the operations on only those objects for which a particular database administration operation is necessary. Although safe (i.e., not missing maintenance on any object) and simple to specify, the first approach is impractical for large databases, and many database users employ the second approach which is an exception-based approach. This exception-based approach requires the user to determine which objects require a particular database administration operation. Typical exceptions requiring database administration operation include:
1. backup too old or non-existing;
2. disorganized data;
3. obsolete statistics used by the optimizer; and
4. little or no space available for object.
To detect objects that are in an exception state, the customer must analyze the database using their own database administration tools, products provided by the DBMS, or utilities available from third-party vendors. These tools collect and report the indicators that help identify objects in the exception state; the exception states fall into three categories: reorganization, backup, and statistics.
Large databases consist of many parallel tasks, allowing parallel processing for many of the database's functions. Since these tasks operate in parallel, they can insert data in the same table or index space, causing poor clustering which requires a reorganization to correct.
In addition, when record updates do not fit in the same page as the original record, the database creates an overflow record and a pointer from the original record to the overflow record. This overflow is space inefficient and time consuming when attempting queries on the data in the database, requiring correction by the reorganization utility. Reorganization is required to optimize the placement of data and arrange data in a clustering sequence and to remove overflow records. The reorganization utility may need to change primary and secondary quantities to remove the secondary extents. Reorganization deletes and defines a new data set, which allows new values to take affect. Prior to reorganization, the primary and secondary quantity values can be altered. The new values take affect during reorganization.
The backup utility creates periodic image copies of the data to maintain security, safety, and integrity of the data. The statistics utility assists the database in efficiently responding to queries. Since multiple indexes can refer to the same table space, the database must choose which path to take to access the data of interest when responding to a query. Accurate and current statistics are required to guide the optimizer in choosing the most efficient paths.
While the exceptions based approach to database administration reduces processing time and complexity, several difficulties remain. To provide exception state indicators, the utilities must examine every object because there is no external indication which objects or table spaces require maintenance. Examining all the objects is costly and time consuming. With no external indication specifying that an object or table space requires maintenance, database administration is often performed needlessly on objects that don't require maintenance, increasing the batch window and data unavailability. Therefore, the utilities are scheduled to run either on demand (after major database maintenance) or periodically such as once a week. Consequently, objects can be in an exception state for a long time before detection.
In addition, the maintenance of objects that are not in an exception state wastes valuable batch window time. Database tools are needed that will automatically flag objects needing maintenance, reducing the amount of processing time required to perform maintenance on the database.
Large databases typically used by large corporations require administration by skilled database administration personnel to manage and maintain the database. However, skilled database administration personnel are becoming increasingly rare and require extensive training. Database tools are needed to automatically recommend maintenance requirement and replace some of the functions currently performed by database administration personnel, reducing the skill level and number of personnel required to maintain the database.
Thus, there is need for a system that will recommend when maintenance is required by an object or table space and that identifies the maintenance utility operations to be performed on that object. The need for such a system has heretofore remained unsatisfied.